package com.example.demo.excelOperate;

import com.example.demo.valueObject.AntiCounterfeiting;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;


/**
 * excel工具类
 */
public class ExcelUtils {

    public static void reviseTimes(File file, int rows) throws IOException {

        FileInputStream in = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(in);
        List<AntiCounterfeiting> lists = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(rows);
        String times = getCellValue(row.getCell(5));
        if(times==null||times.equals("")||times.isEmpty())
        {
            row.getCell(5).setCellValue("1");
        }else
        {
            int time = Integer.valueOf(times);
            time++;
            row.getCell(5).setCellValue(time);
        }
        FileOutputStream os = new FileOutputStream(file);
        workbook.write(os);
        workbook.close();
    }

    public static List<AntiCounterfeiting> getCourseListByExcel(InputStream in) throws Exception {
        Workbook workbook = new XSSFWorkbook(in);
        List<AntiCounterfeiting> lists = new ArrayList<>();
        //读取工作簿的第一张表格
        Sheet sheet = workbook.getSheetAt(0);
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            // 读取单元格内容
            //获得当前行的开始列
            int firstCellNum = row.getFirstCellNum();
            //获得当前行的列数
            int lastCellNum = row.getPhysicalNumberOfCells();
            //循环当前行
            AntiCounterfeiting antiCounterfeiting = new AntiCounterfeiting();
            antiCounterfeiting.setBatchNumber(getCellValue(row.getCell(0)));
            Cell cell1 = row.getCell(1);
            if (cell1 != null) {
                if (cell1.getDateCellValue()!=null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
                    String value = sdf.format(cell1.getDateCellValue());
                    antiCounterfeiting.setBirthDate(value);
                }
            }
            antiCounterfeiting.setId(getCellValue(row.getCell(2)));
            antiCounterfeiting.setDescribe(getCellValue(row.getCell(3)));
            antiCounterfeiting.setSpecifications(getCellValue(row.getCell(4)));
            if(getCellValue(row.getCell(5))==null||getCellValue(row.getCell(5)).isEmpty()||getCellValue(row.getCell(5)).equals(""))
            {
                antiCounterfeiting.setQueryTimes("1");
            }else
            {
                antiCounterfeiting.setQueryTimes(getCellValue(row.getCell(5)));
            }
            lists.add(antiCounterfeiting);
        }
        workbook.close();
        in.close();
        return lists;
    }

    private static String getCellValue(Cell cell) {

        if (cell == null) {
            return "";
        }

        if ("NUMERIC".equals(cell.getCellType().name())) {
            return new BigDecimal(cell.getNumericCellValue()).toString();
        } else if ("STRING".equals(cell.getCellType().name()))
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        else if ("FORMULA".equals(cell.getCellType().name())) {
            return StringUtils.trimToEmpty(cell.getCellFormula());
        } else if ("BLANK".equals(cell.getCellType().name())) {
            return "";
        } else if ("BOOLEAN".equals(cell.getCellType().name())) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if ("ERROR".equals(cell.getCellType().name())) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }
    }

}

